<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>PostgreSQL Python tutorial</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="PostgreSQL, Python, tutorial, database, programming">
<meta name="description" content="This is PostgreSQL Python tutorial. This tutorial covers
the basics of PostgreSQL programming in Python. ">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script>

<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-5536206-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>

</head>

<body>


<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>


<h1>PostgreSQL Python tutorial</h1>


<p>
This is a Python programming tutorial for the PostgreSQL database. It covers the 
basics of PostgreSQL programming with the Python language. 
You might also want to check the <a href="/lang/python/">Python tutorial</a> 
or <a href="/db/postgresqlphp/">PostgreSQL PHP tutorial</a> on ZetCode.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Several libraries exist for connecting to the PostgreSQL database from the Python
language. In this tutorial we will use the <b>psycopg2</b> module. It is a PostgreSQL 
database adapter for the Python programming language. According to the module documentation
it is currently the most popular Python module for the PostgreSQL database. 
It is mostly implemented in C as a libpq wrapper.
</p>


<h2>About PostgreSQL database</h2>

<p>
PostgreSQL is a powerful, open source object-relational database system. It is a multi-user, 
multi-threaded database management system. It runs on multiple platforms including Linux, 
FreeBSD, Solaris, Microsoft Windows and Mac OS X. PostgreSQL is developed by the 
PostgreSQL Global Development Group.
</p>


<h2>Prerequisites</h2>


<p>
To work with this tutorial, we must have Python language, PostgreSQL database and psycopg2
language binding installed on our system.
</p>

<pre>
$ sudo apt-get install postgresql
</pre>

<p>
On an Ubuntu based system we can install the PostgreSQL database using the above command.
</p>

<pre>
$ sudo update-rc.d -f postgresql remove
 Removing any system startup links for /etc/init.d/postgresql ...
   /etc/rc0.d/K21postgresql
   /etc/rc1.d/K21postgresql
   /etc/rc2.d/S19postgresql
   /etc/rc3.d/S19postgresql
   /etc/rc4.d/S19postgresql
   /etc/rc5.d/S19postgresql
   /etc/rc6.d/K21postgresql
</pre>

<p>
If we install the PostgreSQL database from packages, it is automatically added 
to the start up scripts of the operating system. If we are only learning 
to work with the database, it is unnecessary to start the database each 
time we boot the system. The above command removes any system startup 
links for the PostgreSQL database.
</p>

<pre>
$ /etc/init.d/postgresql status
Running clusters: 9.1/main

$ service postgresql status
Running clusters: 9.1/main 
</pre>

<p>
We check if the PostgreSQL server is running. If not, we need to start the server.
</p>

<pre>
$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server        [ OK ]
</pre>

<p>
On Ubuntu Linux we can start the server with the service postgresql start command.
</p>

<pre>
$ sudo service postgresql stop
[sudo] password for janbodnar: 
 * Stopping PostgreSQL 9.1 database server        [ OK ] 
</pre>

<p>
We use the service postgresql stop command to stop the PostgreSQL server.
</p>

<pre>
$ sudo apt-get install python-psycopg2
</pre>

<p>
Here we install the <b>psycopg2</b> module on a Ubuntu system. 
</p>

<pre>
$ sudo -u postgres createuser janbodnar
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
</pre>

<p>
We create a new role in the PostgreSQL system. We allow it to have ability
to create new databases. A <b>role</b> is a user in a database world. Roles
are separate from operating system users. We have created a new user without
the -W option, e.g. we have not specified a password. This enables us
to connect to a database with this user without password authentication. 
Note that this works only on localhost. 
</p>

<pre>
$ sudo -u postgres createdb testdb -O janbodnar
</pre>

<p>
The <code>createdb</code> command creates a new PostgreSQL database with the
owner janbodnar.
</p>


<h2>Version</h2>

<p>
In the first code example, we will get the version of the PostgreSQL database.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    cur = con.cursor()
    cur.execute('SELECT version()')          
    ver = cur.fetchone()
    print ver    
    

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
In the above Python script we connect to the previously created
testdb database. We execute an SQL statement which returns the
version of the PostgreSQL database. 
</p>

<pre class="explanation">
import psycopg2
</pre>

<p>
The <code>psycopg2</code> is a Python module which is used to work 
with the PostgreSQL database.
</p>

<pre class="explanation">
con = None
</pre>

<p>
We initialize the con variable to None. In case we could not create a connection
to the database (for example the disk is full), we would not have a connection
variable defined. This would lead to an error in the finally clause. 
</p>

<pre class="explanation">
con = psycopg2.connect(database='testdb', user='janbodnar') 
</pre>

<p>
The <code>connect()</code> method creates a new database session and
returns a connection object. The user was created without a password. 
On localhost, we can omit the password option. Otherwise, it must
be specified. 
</p>

<pre class="explanation">
cur = con.cursor()
cur.execute('SELECT version()')   
</pre>

<p>
From the connection, we get the cursor object. The cursor is used to traverse 
the records from the result set. We call the <code>execute()</code> method of the cursor 
and execute the SQL statement.
</p>

<pre class="explanation">
er = cur.fetchone()
</pre>

<p>
We fetch the data. Since we retrieve only one record, we call the 
<code>fetchone()</code> method.
</p>

<pre class="explanation">
print ver   
</pre>

<p>
We print the data that we have retrieved to the console.
</p>


<pre class="explanation">
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
</pre>

<p>
In case of an exception, we print an error message and
exit the script with an error code 1. 
</p>

<pre class="explanation">
finally:
    
    if con:
        con.close()) 
</pre>

<p>
In the final step, we release the resources.
</p>

<pre>
$ ./version.py
version                                                                                                     
---------------------------------------------------
---------------------------------------------------------
PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled 
by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 32-bit
(1 row)
</pre>

<p>
Running the version.py script.
</p>



<h2>Inserting data</h2>

<p>
We will create a Cars table and insert several rows to it.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar')    
    
    cur = con.cursor()
  
    cur.execute("CREATE TABLE cars(id INT PRIMARY KEY, name VARCHAR(20), price INT)")
    cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
    cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
    cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
    cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
    cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
    cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
    cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
    cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)")
    
    con.commit()
    

except psycopg2.DatabaseError, e:
    
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
The above script creates a Cars table and inserts 8 rows into the
table. 
</p>

<pre class="explanation">
cur.execute("CREATE TABLE cars(id INT PRIMARY KEY, name VARCHAR(20), price INT)")
</pre>

<p>
This SQL statement creates a new cars table. The table has
three columns. 
</p>

<pre class="explanation">
cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
</pre>

<p>
These two lines insert two cars into the table. 
</p>

<pre class="explanation">
con.commit()
</pre>

<p>
The changes are committed to the database. 
</p>

<pre class="explanation">
if con:
    con.rollback()
</pre>

<p>
In case of an error, we roll back any possible changes
to our database table. 
</p>


<pre>
$ psql testdb
psql (9.1.2)
Type "help" for help.

testdb=> SELECT * FROM cars;
 id |    name    | price  
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21600
(8 rows)
</pre>

<p>
We verify the written data with the psql tool. 
</p>


<hr class="btm">

<p>
We are going to create the same table. This time using the convenience
<code>executemany()</code> method. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


cars = (
    (1, 'Audi', 52642),
    (2, 'Mercedes', 57127),
    (3, 'Skoda', 9000),
    (4, 'Volvo', 29000),
    (5, 'Bentley', 350000),
    (6, 'Citroen', 21000),
    (7, 'Hummer', 41400),
    (8, 'Volkswagen', 21600)
)

con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
  
    cur = con.cursor()  
    
    cur.execute("DROP TABLE IF EXISTS cars")
    cur.execute("CREATE TABLE cars(id INT PRIMARY KEY, name TEXT, price INT)")
    query = "INSERT INTO cars (id, name, price) VALUES (%s, %s, %s)"
    cur.executemany(query, cars)
        
    con.commit()
    

except psycopg2.DatabaseError, e:
    
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
This script drops a Cars table if it exists and (re)creates it.
</p>

<pre class="explanation">
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT PRIMARY KEY, name TEXT, price INT)")
</pre>

<p>
The first SQL statement drops the Cars table, if it exists. The second
SQL statement creates the Cars table. 
</p>

<pre class="explanation">
query = "INSERT INTO cars (id, name, price) VALUES (%s, %s, %s)"
</pre>

<p>
This is the query that we will use. 
</p>

<pre class="explanation">
cur.executemany(query, cars)
</pre>

<p>
We insert 8 rows into the table using the convenience <code>executemany()</code> method. 
The first parameter of this method is a parameterized SQL statement. The second
parameter is the data, in the form of tuple of tuples. 
</p>


<h2>Retrieving data</h2>

<p>
Now, that we have inserted some data into the database, 
we want to get it back.
</p>


<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys

con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar')  
    
    cur = con.cursor()    
    cur.execute("SELECT * FROM cars")

    rows = cur.fetchall()

    for row in rows:
        print row
    

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
In this example, we retrieve all data from the cars table.
</p>

<pre class="explanation">
cur.execute("SELECT * FROM Cars")
</pre>

<p>
This SQL statement selects all data from the Cars table.
</p>

<pre class="explanation">
rows = cur.fetchall()
</pre>

<p>
The <code>fetchall()</code> method gets all records. It returns 
a result set. Technically, it is a tuple of tuples. Each of the inner tuples 
represent a row in the table.
</p>

<pre class="explanation">
for row in rows:
    print row
</pre>

<p>
We print the data to the console, row by row.
</p>

<pre>
$ ./fetch1.py
(1, 'Audi', 52642)
(2, 'Mercedes', 57127)
(3, 'Skoda', 9000)
(4, 'Volvo', 29000)
(5, 'Bentley', 350000)
(6, 'Citroen', 21000)
(7, 'Hummer', 41400)
(8, 'Volkswagen', 21600)
</pre>

<p>
This is the output of the example.
</p>

<hr class="btm">

<p>
Returning all data at a time may not be feasible. We can fetch rows one by one.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys

con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    cur = con.cursor()     
    cur.execute("SELECT * FROM cars")

    while True:
      
        row = cur.fetchone()
        
        if row == None:
            break
            
        print row[0], row[1], row[2]
    

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
In this script we connect to the database and fetch the rows
of the cars table one by one. 
</p>

<pre class="explanation">
while True:
</pre>

<p>
We access the data from the while loop. When we read the last row,
the loop is terminated.
</p>

<pre class="explanation">
row = cur.fetchone()

if row == None:
    break
</pre>

<p>
The <code>fetchone()</code> method returns the next row from 
the table. If there is no more data left, it returns <code>None</code>. 
In this case we break the loop.
</p>

<pre class="explanation">
print row[0], row[1], row[2]
</pre>

<p>
The data is returned in the form of a tuple. Here we select 
records from the tuple. The first is the Id, the second
is the car name and the third is the price of the car.
</p>


<pre>
$ ./retrieveonebyone.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
</pre>

<p>
This is the output of the example.
</p>


<h2>The dictionary cursor</h2>

<p>
The default cursor returns the data in a tuple of tuples. 
When we use a dictionary cursor, the data is sent in a form of Python dictionaries. 
This way we can refer to the data by their column names.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import psycopg2.extras
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    cursor = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cursor.execute("SELECT * FROM Cars")
    
    rows = cursor.fetchall()

    for row in rows:
        print "%s %s %s" % (row["id"], row["name"], row["price"])
   

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
In this example, we print the contents of the cars table 
using the dictionary cursor.
</p>

<pre class="explanation">
import psycopg2.extras
</pre>

<p>
The dictionary cursor is located in the extras module. 
</p>

<pre class="explanation">
cursor = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
</pre>

<p>
We create a <code>DictCursor</code>. 
</p>

<pre class="explanation">
for row in rows:
    print "%s %s %s" % (row["id"], row["name"], row["price"])
</pre>

<p>
The data is accessed by the column names. 
</p>



<h2>Parameterized queries</h2>

<p>
Now we will concern ourselves with parameterized queries. When we 
use parameterized queries, we use placeholders instead of directly 
writing the values into the statements. Parameterized queries increase 
security and performance.
</p>

<p>
The Python psycopg2 module supports two types of placeholders. Ansi C printf
format and Python extended format. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

uId = 1
uPrice = 62300 

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar')  
    
    cur = con.cursor()

    cur.execute("UPDATE Cars SET price=%s WHERE id=%s", (uPrice, uId))        
    con.commit()
    
    print "Number of rows updated: %d" % cur.rowcount
       

except psycopg2.DatabaseError, e:
    
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
We update a price of one car. In this code example, we use the question
mark placeholders. 
</p>

<pre class="explanation">
cur.execute("UPDATE Cars SET price=%s WHERE id=%s", (uPrice, uId))  
</pre>

<p>
The characters (%s) are placeholders for values. The values are
added to the placeholders. 
</p>

<pre class="explanation">
print "Number of rows updated: %d" % cur.rowcount
</pre>

<p>
The <code>rowcount</code> property returns the number of updated 
rows. In our case one row was updated. 
</p>

<pre>
$ ./prepared.py
Number of rows updated: 1

testdb=> SELECT * FROM cars WHERE id=1;
 id | name | price 
----+------+-------
  1 | Audi | 62300
(1 row)
</pre>

<p>
The price of the car was updated. We check the change with the psql tool.
</p>

<hr class="btm">

<p>
The second example uses parameterized statements with Python extended format.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

uid = 3

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar')  
    
    cur = con.cursor()

    cur.execute("SELECT * FROM cars WHERE id=%(id)s", {'id': uid } )
    
    print cur.fetchone()
   

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
We select a name and a price of a car using pyformat parameterized 
statement.
</p>

<pre class="explanation">
cur.execute("SELECT * FROM cars WHERE id=%(id)s", {'id': uid } )
</pre>

<p>
The named placeholders start with a colon character. 
</p>

<pre>
$ ./parameterized2.py
(3, 'Skoda', 9000)
</pre>

<p>
Output of the example. 
</p>


<h2>Inserting images</h2>

<p>
In this section, we are going to insert an image to the 
PostgreSQL database. Note that some people argue against putting 
images into databases. Here we only show how to do it. We do not
dwell into technical issues of whether to save images in
databases or not. 
</p>

<pre>
testdb=> CREATE TABLE images(id INT PRIMARY KEY, data BYTEA);
</pre>

<p>
For this example, we create a new table called images. For the images, we use
the <code>BYTEA</code> data type. It allows to store binary strings. 
</p>


<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


def readImage():

    try:
        fin = open("woman.jpg", "rb")
        img = fin.read()
        return img
        
    except IOError, e:

        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

    finally:
        
        if fin:
            fin.close()


try:
    con = psycopg2.connect(database="testdb", user="janbodnar") 
    
    cur = con.cursor()
    data = readImage()
    binary = psycopg2.Binary(data)
    cur.execute("INSERT INTO images(id, data) VALUES (1, %s)", (binary,) )

    con.commit()    
    
except psycopg2.DatabaseError, e:

    if con:
        con.rollback()

    print 'Error %s' % e    
    sys.exit(1)
    
finally:
    
    if con:
        con.close()   
</pre>

<p>
In this script, we read an image from the current working directory 
and write it into the images table of the PostgreSQL testdb database. 
</p>

<pre class="explanation">
try:
    fin = open("woman.jpg", "rb")
    img = fin.read()
    return img
</pre>

<p>
We read binary data from the filesystem. We have a jpg image
called woman.jpg. 
</p>

<pre class="explanation">
binary = psycopg2.Binary(data)
</pre>

<p>
The data is encoded using the psycopg2 <code>Binary</code> object. 
</p>

<pre class="explanation">
cur.execute("INSERT INTO images(id, data) VALUES (1, %s)", (binary,) )
</pre>

<p>
This SQL statement is used to insert the image into the database. 
</p>


<h2>Reading images</h2>

<p>
In this section, we are going to perform the reverse operation.
We will read an image from the database table. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


def writeImage(data):
    
    try:
        fout = open('woman2.jpg','wb')
        fout.write(data)
    
    except IOError, e:    
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
        
    finally:
        
        if fout:
            fout.close()  


try:
    con = psycopg2.connect(database="testdb", user="janbodnar") 
    
    cur = con.cursor()    
    cur.execute("SELECT Data FROM images LIMIT 1")
    data = cur.fetchone()[0]
    
    writeImage(data)

    con.commit()    
    
except psycopg2.DatabaseError, e:

    if con:
        con.rollback()

    print 'Error %s' % e    
    sys.exit(1)
    
finally:
    
    if con:
        con.close()       
</pre>

<p>
We read image data from the images table and write it
to another file, which we call woman2.jpg. 
</p>

<pre class="explanation">
try:
    fout = open('woman2.jpg','wb')
    fout.write(data)
</pre>

<p>
We open a binary file in a writing mode. The data
from the database is written to the file. 
</p>

<pre class="explanation">
cur.execute("SELECT Data FROM Images LIMIT 1")
data = cur.fetchone()[0]
</pre>

<p>
These two lines select and fetch data from the Images
table. We obtain the binary data from the first row. 
</p>


<h2>Metadata</h2>

<p>
Metadata is information about the data in the database. 
Metadata in a PostgreSQL database contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is a metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<p>
Metadata in PostgreSQL can be obtained using from the <b>description</b> property of
the cursor object or from the <b>information_schema</b> table. 
</p>


<p>
Next we will print all rows from the cars table with their column names.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect("dbname='testdb' user='janbodnar'") 
    
    cur = con.cursor() 
    cur.execute('SELECT * FROM cars')    
    col_names = [cn[0] for cn in cur.description]
    
    rows = cur.fetchall()
    
    print "%s %-10s %s" % (col_names[0], col_names[1], col_names[2])

    for row in rows:    
        print "%2s %-10s %s" % row
    
   

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
We print the contents of the cars table to the console. 
Now, we include the names of the columns too. The records are aligned
with the column names. 
</p>

<pre class="explanation">
col_names = [cn[0] for cn in cur.description]
</pre>

<p>
We get the column names from the <code>description</code> property
of the cursor object. 
</p>

<pre class="explanation">
print "%s %-10s %s" % (col_names[0], col_names[1], col_names[2])
</pre>

<p>
This line prints three column names of the cars table. 
</p>

<pre class="explanation">
for row in rows:    
    print "%2s %-10s %s" % row
</pre>

<p>
We print the rows using the for loop. The data
is aligned with the column names. 
</p>

<pre>
$ ./colnames.py
id name       price
 2 Mercedes   57127
 3 Skoda      9000
 4 Volvo      29000
 5 Bentley    350000
 6 Citroen    21000
 7 Hummer     41400
 8 Volkswagen 21600
 1 Audi       62300
</pre>

<p>
Output. 
</p>

<hr class="btm">

<p>
In the following example we will list all tables in the testdb database. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    cur = con.cursor() 
    cur.execute("""SELECT table_name FROM information_schema.tables 
       WHERE table_schema = 'public'""")    
        
    rows = cur.fetchall()

    for row in rows:
        print row[0]
    
   
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
The code example prints all available tables in the current database
to the terminal. 
</p>

<pre class="explanation">
cur.execute("""SELECT table_name FROM information_schema.tables 
    WHERE table_schema = 'public'""") 
</pre>

<p>
The table names are stored inside the system <code>information_schema</code> table.
</p>

<pre>
$ ./list_tables.py
cars
images
friends
</pre>

<p>
These were the tables on our system. 
</p>


<h2>Export and import of data</h2>

<p>
We can export and import data using <code>copy_to()</code> 
and <code>copy_from()</code> methods. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None
fout = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    cur = con.cursor()
    fout = open('cars.sql','w')
    cur.copy_to(fout, 'cars', sep="|")                        
   

except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)

except IOError, e:    
    print 'Error %s' % e   
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

    if fout:
        fout.close() 
</pre>

<p>
In the above example, we copy the data from the cars table into
the cars.sql file. 
</p>

<pre class="explanation">
fout = open('cars.sql','w')
</pre>

<p>
We open a file where we will write the data from the cars
table. 
</p>

<pre class="explanation">
cur.copy_to(fout, 'cars', sep="|")   
</pre>

<p>
The <code>copy_to</code> method copies data from the cars table
to the opened file. The columns are separated with a | character.
</p>

<pre>
$ cat cars.sql 
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600
1|Audi|62300
</pre>

<p>
The contents of the cars.sql file. 
</p>

<hr class="btm">

<p>
Now we are going to perform a reverse operation. We will import
the dumped table back into the database table. 
</p>

<pre>
testdb=> DELETE FROM cars;
DELETE 8
</pre>

<p>
We delete the data from the cars table. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None
f = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    cur = con.cursor()
    f = open('cars', 'r')
    cur.copy_from(f, 'cars', sep="|")                    
    con.commit()
   
except psycopg2.DatabaseError, e:
    
    if con:
        con.rollback()
    
    print 'Error %s' % e    
    sys.exit(1)

except IOError, e:    

    if con:
        con.rollback()

    print 'Error %s' % e   
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

    if f:
        f.close()  
</pre>

<p>
In this script, we read the contents of the cars file
and copy it back to the cars table. 
</p>

<pre class="explanation">
f = open('cars', 'r')
cur.copy_from(f, 'cars', sep="|")                    
con.commit()
</pre>

<p>
We open the cars file for reading and copy the contents to the
cars table. The changes are committed. 
</p>


<pre>
testdb=> SELECT * FROM cars;
 id |    name    | price  
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21600
(8 rows)
</pre>

<p>
The output shows, that we have successfully 
recreated the saved cars table. 
</p>


<h2>Transactions</h2>

<p>
A transaction is an atomic unit of database operations against 
the data in one or more databases. The effects of all the SQL 
statements in a transaction can be either all committed 
to the database or all rolled back.
</p>

<p>
In psycopg2 module transactions are handled by the connection class. 
The first command of a connection cursor starts a transaction. (We do not need to enclose 
our SQL commands by BEGIN and END statements to create a transaction. 
This is handled automatically by psycopg2.) The
following commands are executed in the context of this new 
transaction. In case of an error, the transaction is aborted and
no further commands are executed until the <code>rollback()</code> method.
</p>

<p>
The documentation to the psycopg2 module says that the connection is responsible 
to terminate its transaction, calling either the 
<code>commit()</code> or <code>rollback()</code> method. Committed changes are immediately made persistent 
into the database. Closing the connection using the <code>close()</code> method or destroying 
the connection object (using del or letting it fall out of scope) will 
result in an implicit <code>rollback()</code> call. 
</p>

<p>
The psycopg2 module also supports an autocommit mode,
where all changes to the tables are immediately effective.
To run in autocommit mode, we set the <b>autocommit</b>
property of the connection object to True. 
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    print con.autocommit
    
    cur = con.cursor() 

    cur.execute("DROP TABLE IF EXISTS friends")
    cur.execute("CREATE TABLE friends(id serial PRIMARY KEY, name VARCHAR(10))")
    cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
    cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
    cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
    cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
    
    #con.commit()
       
except psycopg2.DatabaseError, e:
    
    if con:
        con.rollback()
        
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
We create a friends table and try to fill it with data. However, as we will see,
the data will be not committed. 
</p>

<pre class="explanation">
#con.commit()
</pre>

<p>
The <code>commit()</code> method is commented. If we uncomment the
line, the data will be written to the table. 
</p>

<pre class="explanation">
finally:
    
    if con:
        con.close()
</pre>

<p>
The finally block is always executed. If we have not committed the
changes and no error occures (which would roll back the changes)
the transaction is still opened. The connection is closed with the
<code>close()</code> method and the transaction is
terminated with an implicit call to the <code>rollback()</code> method.
</p>

<pre>
testdb=> \dt
          List of relations
 Schema |  Name   | Type  |   Owner   
--------+---------+-------+-----------
 public | cars    | table | janbodnar
 public | friends | table | janbodnar
 public | images  | table | janbodnar
(3 rows)
</pre>

<p>
Only after we have uncommented the line, the friends table
is created. 
</p>

<hr class="btm">

<p>
In the autocommit mode, an SQL statement is executed immediately.
</p>

<pre class="code">
#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys


con = None

try:
     
    con = psycopg2.connect(database='testdb', user='janbodnar') 
    
    con.autocommit = True
    
    cur = con.cursor() 

    cur.execute("DROP TABLE IF EXISTS friends")
    cur.execute("CREATE TABLE friends(id serial PRIMARY KEY, name VARCHAR(10))")
    cur.execute("INSERT INTO friends(name) VALUES ('Jane')")
    cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
    cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
    cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
    cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
    cur.execute("INSERT INTO friends(name) VALUES ('Patrick')")
    
      
except psycopg2.DatabaseError, e:
            
    print 'Error %s' % e    
    sys.exit(1)
    
    
finally:
    
    if con:
        con.close()
</pre>

<p>
In this example, we connect to the database in the autocommit mode. 
We don't call neither <code>commit()</code> nor <code>rollback()</code> 
methods.
</p>


<pre class="explanation">
con.autocommit = True
</pre>

<p>
We set the connection to the autocommit mode. 
</p>

<pre>
$ ./autocommit.py

testdb=> SELECT * FROM friends;
 id |  name   
----+---------
  1 | Jane
  2 | Tom
  3 | Rebecca
  4 | Jim
  5 | Robert
  6 | Patrick
(6 rows)
</pre>

<p>
The data was successfully committed to the friends table. 
</p>

<p>
This was the PostgreSQL Python tutorial. 
</p>

<div class="g-plusone"></div>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 31, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

